1 Public Class FrmDEFFECTIVE_RETURN_STOCKS
2
3 Private Sub FrmDEFFECTIVE_RETURN_STOCKS_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
4 MDIDISABLED()
5 End Sub
6
7 Private Sub FrmDEFFECTIVE_STOCKS_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
8 If rbDeffect.Checked Then
9 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase No', Replace(Replace(SupplierName,'$.$',''''),'$..$',',') as 'Supplier Name', Replace(Replace(Delivery_Term,'$.$',''''),'$..$',',') as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',',') AS 'Address', Pending_ID AS 'Pending ID' " & _
10 "FROM TBL_Deffective_PO " & _
11 "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "' ORDER BY Def_PO_ID ASC"
12 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
13 For i = 0 To lstdeffect.Items.Count - 1
14 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Return WHERE Def_PO_ID =" & lstdeffect.Items(i).Text & _
15 " AND Fully_Return='Yes'" & _
16 " ORDER BY Def_PO_ID ASC"
17 ExecuteSQLQuery(sqlSTR)
18 If sqlDT.Rows.Count > 0 Then
19 lstdeffect.Items(i).ForeColor = Color.Brown
20 Else
21 lstdeffect.Items(i).ForeColor = Color.Black
22 End If
23 Next
24 Else
25 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase No', Replace(Replace(SupplierName,'$.$',''''),'$..$',',') as 'Supplier Name', Replace(Replace(Delivery_Term,'$.$',''''),'$..$',',') as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',',') AS 'Address' FROM TBL_Deffective_PO_Return " & _
26 "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "'"
27 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
28 End If
29 Audit_Trail(xUser_ID, TimeOfDay, "View Deffective and Return Stocks")
30 End Sub
31
32 Private Sub dtreturn_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtreturn.ValueChanged
33 If rbDeffect.Checked Then
34 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase ID', Replace(Replace(SupplierName,'$.$',''''),'$..$',',') as 'Supplier Name', Replace(Replace(Delivery_Term,'$.$',''''),'$..$',',') as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',','), Pending_ID AS 'Pending ID' FROM TBL_Deffective_PO " & _
35 "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "'"
36 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
37 For i = 0 To lstdeffect.Items.Count - 1
38 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Return WHERE Def_PO_ID =" & lstdeffect.Items(i).Text & _
39 " AND Fully_Return='Yes'" & _
40 " ORDER BY Def_PO_ID ASC"
41
42 ExecuteSQLQuery(sqlSTR)
43 If sqlDT.Rows.Count > 0 Then
44 lstdeffect.Items(i).ForeColor = Color.Brown
45 Else
46 lstdeffect.Items(i).ForeColor = Color.Black
47 End If
48 Next
49 ElseIf rbReturn.Checked Then
50 'x123
51 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase No', Replace(Replace(SupplierName,'$.$',''''),'$..$',',') as 'Supplier Name', Replace(Replace(Delivery_Term,'$.$',''''),'$..$',',') as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',',') AS 'Address', Fully_Return AS 'Return' FROM TBL_Deffective_PO_Return " & _
52 "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "' ORDER BY DEF_PO_ID ASC"
53 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
54 For i = 0 To lstdeffect.Items.Count - 1
55 'MsgBox(.lstdeffect.Items(i).SubItems(5).Text)
56 If lstdeffect.Items(i).SubItems(5).Text = "Yes" Then
57 lstdeffect.Items(i).ForeColor = Color.Brown
58 Else
59 lstdeffect.Items(i).ForeColor = Color.Black
60 End If
61 Next
62 ElseIf rbpending.Checked Then
63 sqlSTR = "SELECT Pending_ID as 'Pending ID', Replace(Replace(Item_Name,'$.$',''''),'$..$',',') as 'Name', Replace(Replace(Item_Description,'$.$',''''),'$..$',',') as 'Description / Item Number',Pending_Date as 'Date', Item_QTY as 'Quantity', Receipt_ID AS 'Receipt No' " & _
64 "FROM TBL_Pending_Item " & _
65 "INNER JOIN TBL_Category_Item_File ON TBL_Pending_Item.Item_ID = TBL_Category_Item_File.Item_ID " & _
66 "WHERE Returnx = 'No' AND Pending_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "'"
67 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
68 For x = 0 To lstdeffect.Items.Count - 1
69 'MsgBox(lstdeffect.Items(x).Text)
70 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Details " & _
71 "INNER JOIN TBL_Deffective_PO_Return ON TBL_Deffective_PO_Details.DEF_PO_ID = TBL_Deffective_PO_Return.DEF_PO_ID " & _
72 "INNER JOIN TBL_Deffective_PO_Return_Details ON TBL_Deffective_PO_Return.Return_ID = TBL_Deffective_PO_Return_Details.Return_ID " & _
73 "INNER JOIN TBL_Category_Item_File ON TBL_Deffective_PO_Return_Details.Item_ID = TBL_Category_Item_File.Item_ID " & _
74 "WHERE TBL_Deffective_PO_Details.Pending_ID =" & lstdeffect.Items(x).Text
75 ExecuteSQLQuery(sqlSTR)
76 If sqlDT.Rows.Count > 0 Then
77 lstdeffect.Items(x).ForeColor = Color.Brown
78 Else
79 lstdeffect.Items(x).ForeColor = Color.Black
80 End If
81 Next
82 End If
83 End Sub
84
85 Private Sub rbDeffect_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbDeffect.CheckedChanged
86 If rbDeffect.Checked Then
87 With MDIMain
88 .cmdNew.Enabled = True
89 .cmdEdit.Enabled = True
90 .cmdPrint.Enabled = True
91 .cmdSearch.Enabled = True
92 '.ToolStripNew.Enabled = True
93 '.ToolStripEdit.Enabled = True
94 '.ToolStripPrint.Enabled = True
95 '.ToolStripSearch.Enabled = True
96 'sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase No', Replace(Replace(SupplierName,'$.$',''''),'$..$',',') as 'Supplier Name', Replace(Replace(Delivery_Term,'$.$',''''),'$..$',',') as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',',') AS 'Address', Pending_ID AS 'Pending ID' FROM TBL_Deffective_PO " & _
97 ' "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "' ORDER BY Def_PO_ID ASC"
98
99
100 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase No', Replace(Replace(SupplierName,'$.$',''''),'$..$',',') as 'Supplier Name', Replace(Replace(Delivery_Term,'$.$',''''),'$..$',',') as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',',') AS 'Address', Pending_ID AS 'Pending ID' " & _
101 "FROM TBL_Deffective_PO " & _
102 "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "' ORDER BY Def_PO_ID ASC"
103 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
104
105 For i = 0 To lstdeffect.Items.Count - 1
106 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Return WHERE Def_PO_ID =" & lstdeffect.Items(i).Text & _
107 " AND Fully_Return='Yes'" & _
108 " ORDER BY Def_PO_ID ASC"
109
110 ExecuteSQLQuery(sqlSTR)
111 If sqlDT.Rows.Count > 0 Then
112 lstdeffect.Items(i).ForeColor = Color.Brown
113 Else
114 lstdeffect.Items(i).ForeColor = Color.Black
115 End If
116 Next
117 ' sqlSTR = "SELECT TBL_Deffective_PO.def_po_id AS 'DEF_ID', *, *, * " & _
118 ' "FROM TBL_Deffective_PO " & _
119 ' "INNER JOIN TBL_Deffective_PO_Details ON TBL_Deffective_PO.Def_PO_ID = TBL_Deffective_PO_Details.Def_PO_ID " & _
120 ' "INNER JOIN TBL_Deffective_PO_Return ON TBL_Deffective_PO.Def_PO_ID = TBL_Deffective_PO_Return.Def_PO_ID " & _
121 ' "INNER JOIN TBL_Deffective_PO_Return_Details ON TBL_Deffective_PO_Return.Return_ID = TBL_Deffective_PO_Return_Details.Return_ID " & _
122 ' "WHERE TBL_Deffective_PO.Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & _
123 ' "' ORDER BY TBL_Deffective_PO.Def_PO_ID ASC"
124
125 ' ExecuteSQLQuery(sqlSTR)
126
127 ' If sqlDT.Rows.Count > 0 Then
128 ' For x = 0 To sqlDT.Rows.Count - 1
129 ' If CDbl(sqlDT.Rows(x)("Def_QTY")) = CDbl(sqlDT.Rows(x)("Return_QTY")) Then
130 ' 'lstdeffect.Items(x).ForeColor = Color.Brown
131 ' Else
132 ' 'lstdeffect.Items(x).ForeColor = Color.DarkBlue
133 ' End If
134 ' Next
135 'End If
136
137 End With
138 End If
139
140 End Sub
141
142 Private Sub rbReturn_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbReturn.CheckedChanged
143 If rbReturn.Checked Then
144 With MDIMain
145 .cmdNew.Enabled = True
146 .cmdEdit.Enabled = True
147 .cmdPrint.Enabled = True
148 .cmdSearch.Enabled = True
149 '.ToolStripNew.Enabled = True
150 '.ToolStripEdit.Enabled = True
151 '.ToolStripPrint.Enabled = True
152 '.ToolStripSearch.Enabled = True
153 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase No', Replace(Replace(SupplierName,'$.$',''''),'$..$',',') as 'Supplier Name', Replace(Replace(Delivery_Term,'$.$',''''),'$..$',',') as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',',') AS 'Address', Fully_Return AS 'Return' FROM TBL_Deffective_PO_Return " & _
154 "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "' ORDER BY DEF_PO_ID ASC"
155 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
156 For i = 0 To lstdeffect.Items.Count - 1
157 'MsgBox(.lstdeffect.Items(i).SubItems(5).Text)
158 If lstdeffect.Items(i).SubItems(5).Text = "Yes" Then
159 lstdeffect.Items(i).ForeColor = Color.Brown
160 Else
161 lstdeffect.Items(i).ForeColor = Color.Black
162 End If
163 Next
164 'sqlSTR = "SELECT * FROM TBL_Deffective_PO_Return " & _
165 ' "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "' ORDER BY DEF_PO_ID ASC"
166 'ExecuteSQLQuery(sqlSTR)
167 'For i = 0 To sqlDT.Rows.Count - 1
168 ' 'MsgBox(sqlDT.Rows(i)("Fully_Return"))
169 ' If CDbl(sqlDT.Rows(i)("Fully_Return")) > 0 Then
170 ' lstdeffect.Items(i).BackColor = Color.Brown
171 ' Else
172 ' lstdeffect.Items(i).BackColor = Color.Black
173 ' End If
174 'Next
175 End With
176 End If
177 End Sub
178
179 Private Sub rbpending_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbpending.CheckedChanged
180 Dim xGo As Boolean
181 If rbpending.Checked Then
182 With MDIMain
183 .cmdPrint.Enabled = False
184 .cmdSearch.Enabled = False
185 .cmdEdit.Enabled = False
186 .cmdNew.Enabled = False
187 '.ToolStripPrint.Enabled = False
188 '.ToolStripSearch.Enabled = False
189 '.ToolStripEdit.Enabled = False
190 '.ToolStripNew.Enabled = False
191 End With
192 sqlSTR = "SELECT Pending_ID as 'Pending ID', Replace(Replace(Item_Name,'$.$',''''),'$..$',',') as 'Name', Replace(Replace(Item_Description,'$.$',''''),'$..$',',') as 'Description / Item Number',Pending_Date as 'Date', Item_QTY as 'Quantity', Receipt_ID AS 'Receipt No' " & _
193 "FROM TBL_Pending_Item " & _
194 "INNER JOIN TBL_Category_Item_File ON TBL_Pending_Item.Item_ID = TBL_Category_Item_File.Item_ID " & _
195 "WHERE Returnx = 'No' AND Pending_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "'"
196 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
197 For x = 0 To lstdeffect.Items.Count - 1
198
199 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Details " & _
200 "INNER JOIN TBL_Deffective_PO_Return ON TBL_Deffective_PO_Details.DEF_PO_ID = TBL_Deffective_PO_Return.DEF_PO_ID " & _
201 "INNER JOIN TBL_Deffective_PO_Return_Details ON TBL_Deffective_PO_Return.Return_ID = TBL_Deffective_PO_Return_Details.Return_ID " & _
202 "INNER JOIN TBL_Category_Item_File ON TBL_Deffective_PO_Return_Details.Item_ID = TBL_Category_Item_File.Item_ID " & _
203 "WHERE TBL_Deffective_PO_Details.Pending_ID =" & lstdeffect.Items(x).Text
204 ExecuteSQLQuery(sqlSTR)
205 If sqlDT.Rows.Count > 0 Then
206 For i = 0 To sqlDT.Rows.Count - 1
207 'MsgBox(sqlDT.Rows(i)("DEF_QTY") & " " & sqlDT.Rows(i)("Return_QTY"))
208 If CDbl(sqlDT.Rows(i)("Def_QTY")) = CDbl(sqlDT.Rows(i)("Return_QTY")) Then
209 lstdeffect.Items(x).ForeColor = Color.Brown
210 Else
211 xGo = True
212 lstdeffect.Items(x).ForeColor = Color.DarkBlue
213 End If
214 Next
215 Else
216 If Not xGo Then
217 sqlSTR = "SELECT * FROM TBL_Deffective_PO WHERE Pending_ID =" & lstdeffect.Items(x).Text
218 ExecuteSQLQuery(sqlSTR)
219 If sqlDT.Rows.Count > 0 Then
220 lstdeffect.Items(x).ForeColor = Color.YellowGreen
221 Else
222 lstdeffect.Items(x).ForeColor = Color.Black
223 End If
224 End If
225 End If
226 Next
227 End If
228 End Sub
229
230 Private Sub cmdcancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdcancel.Click
231 grpCat.Visible = False
232 End Sub
233
234 Private Sub CmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdSearch.Click
235 If rbDeffect.Checked Then
236 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase ID', SupplierName as 'Supplier Name', Delivery_Term as 'Delivery Term', Address FROM TBL_Deffective_PO " & _
237 "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "' AND SupplierName LIKE '%" & txtname.Text & "%'"
238 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
239 Else
240 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase ID', SupplierName as 'Supplier Name', Delivery_Term as 'Delivery Term', Address FROM TBL_Deffective_PO_Return " & _
241 "WHERE Return_Date ='" & Format(dtreturn.Value, "MM/dd/yyyy") & "' AND SupplierName LIKE '%" & txtname.Text & "%'"
242 FillListView(ExecuteSQLQuery(sqlSTR), lstdeffect, 0)
243 End If
244 grpCat.Visible = False
245 End Sub
246
247 Private Sub FrmDEFFECTIVE_RETURN_STOCKS_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Resize
248 With Me
249 GroupBox1.Width = (.Width - (MDIMain.TSHoldRight.Width / 2)) + 60
250 GroupBox1.Height = .Height - (MDIMain.TSHoldAdvisory.Height + 45)
251 .lstdeffect.Width = GroupBox1.Width - 10
252 .lstdeffect.Height = GroupBox1.Height - 45
253
254 .rbReturn.Left = (GroupBox1.Width - .rbReturn.Width) - 2
255 .rbDeffect.Left = (.rbReturn.Left - .rbDeffect.Width) - 4
256 .rbpending.Left = (.rbDeffect.Left - .rbpending.Width) - 4
257
258 .dtreturn.Left = (.rbpending.Left - .dtreturn.Width) - 18
259 .Label3.Left = (.dtreturn.Left - .Label3.Width) - 4
260
261 End With
262 End Sub
263 End Class
